package com.example.database.util;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvValidationException;

public class CsvFileReader
{
    /**
     * 从csv文件中读取数据并插入表中
     * @param conn 数据库连接
     * @param table 要插入的数据表
     * @param filepath csv文件路径
     * @param charsetName 编码
     * @param ignoreError 是否忽略发生错误的行
     * @throws SQLException
     * @throws CsvValidationException
     * @throws IOException
     */
    public void insertIntoTableFromCSVFile(Connection conn, String table, String filepath, String charsetName, boolean ignoreError) throws SQLException, CsvValidationException, IOException
    {
        // 是否忽略发生错误的行
        String sql = "insert " + (ignoreError?"ignore ":"") + "into " + table + "(";

        try (InputStream is = this.getClass().getResourceAsStream(filepath);
             CSVReader csvReader = new CSVReader(new InputStreamReader(is, charsetName)))
        {
            // 生成sql
            String resultSql = generateSql(csvReader);
            if (resultSql.isEmpty())
            {
                return;
            }
            sql += resultSql;
            // 执行sql
            executeSql(csvReader, conn, sql);
        }
    }

    /**
     * 生成sql语句
     * @param csvReader
     * @return
     * @throws CsvValidationException
     * @throws IOException
     */
    private String generateSql(CSVReader csvReader) throws CsvValidationException, IOException
    {
        String[] nextLine;
        StringBuilder sql = new StringBuilder();
        // 读取第一行
        if ((nextLine = csvReader.readNext()) != null)
        {
            // 确定数据表各个列的顺序
            for (String e : nextLine)
            {
                sql.append(e + ",");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(") values (");
            // 拼接带占位符的sql语句
            for (int i = 0; i < nextLine.length; i++)
            {
                sql.append("?,");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(");");
        }
        return sql.toString();
    }

    /**
     * 读取数据插入表中
     * @param csvReader
     * @param conn
     * @param sql
     * @throws SQLException
     * @throws IOException
     * @throws CsvValidationException
     */
    private void executeSql(CSVReader csvReader, Connection conn, String sql) throws SQLException, CsvValidationException, IOException
    {
        String[] nextLine;
        try (PreparedStatement preStmt = conn.prepareStatement(sql);)
        {
            // 读取其他行数据
            while ((nextLine = csvReader.readNext()) != null)
            {
                for (int i = 0; i < nextLine.length; i++)
                {
                    preStmt.setString(i + 1, nextLine[i]);
                }
                preStmt.addBatch();
            }
            // 执行
            conn.setAutoCommit(false);
            preStmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);
        }
        catch (SQLException e)
		{
            e.printStackTrace();
			// 回滚
			conn.rollback();
			throw new SQLException();
		}
    }
}
